﻿IF OBJECT_ID('[tempdb]..[#SchemaModificationErrors]') IS NOT NULL
BEGIN
  DROP TABLE [#SchemaModificationErrors]
END
GO
CREATE TABLE [#SchemaModificationErrors] (
  [Error] [int] NOT NULL,
  [Message] [varchar] (MAX) NULL,
)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION 
GO
/* ---------------------------------------- *\
|* Storing data from GP.QuickFiltri (Table) *|
\* ---------------------------------------- */

BEGIN TRY

CREATE TABLE [__SE_Storage_QuickFiltri_235147883_20130507191458] (
  [IDFiltro] [int] NULL,
  [Applicazione] [char] (5) NULL);
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

INSERT
  INTO [__SE_Storage_QuickFiltri_235147883_20130507191458] ([IDFiltro], [Applicazione])
SELECT [IDFiltro], [Applicazione]
  FROM [GP].[QuickFiltri];
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------------------------------------ *\
|* Dropping object GP.FK_QuickFiltriFamiglie_QuickFiltri (Foreign Key Constraint) *|
\* ------------------------------------------------------------------------------ */

BEGIN TRY

IF EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N'[GP].[FK_QuickFiltriFamiglie_QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsForeignKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltriFamiglie] DROP CONSTRAINT [FK_QuickFiltriFamiglie_QuickFiltri]'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------------------------------------ *\
|* Dropping object GP.FK_QuickFiltriImpianti_QuickFiltri (Foreign Key Constraint) *|
\* ------------------------------------------------------------------------------ */

BEGIN TRY

IF EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N'[GP].[FK_QuickFiltriImpianti_QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsForeignKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltriImpianti] DROP CONSTRAINT [FK_QuickFiltriImpianti_QuickFiltri]'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ---------------------------------------------------------- *\
|* Dropping object GP.PK_QuickFiltri (Primary Key Constraint) *|
\* ---------------------------------------------------------- */

BEGIN TRY

IF EXISTS (SELECT * FROM [sys].[key_constraints] WHERE [object_id] = OBJECT_ID(N'[GP].[PK_QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsPrimaryKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltri] DROP CONSTRAINT [PK_QuickFiltri]'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------------------------- *\
|* Dropping object GP.ReportDirezionale2 (Stored Procedure) *|
\* -------------------------------------------------------- */

BEGIN TRY

IF EXISTS (SELECT * FROM [sys].[procedures] WHERE [object_id] = OBJECT_ID(N'[GP].[ReportDirezionale2]') AND [type] = 'P')
EXEC sp_executesql N'DROP PROCEDURE [GP].[ReportDirezionale2]'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ----------------------------------------------------------------------- *\
|* Dropping object GP.FK_QuickFiltri_AnagFamiglie (Foreign Key Constraint) *|
\* ----------------------------------------------------------------------- */

BEGIN TRY

IF EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N'[GP].[FK_QuickFiltri_AnagFamiglie]') AND OBJECTPROPERTY([object_id], 'IsForeignKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltri] DROP CONSTRAINT [FK_QuickFiltri_AnagFamiglie]'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------- *\
|* Dropping object GP.QuickFiltri (Table) *|
\* -------------------------------------- */

BEGIN TRY

IF EXISTS (SELECT * FROM [sys].[tables] WHERE [object_id] = OBJECT_ID(N'[GP].[QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsUserTable') = 1)
EXEC sp_executesql N'DROP TABLE [GP].[QuickFiltri]'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ---------------------------------- *\
|* Creating object GP.Formule (Table) *|
\* ---------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[tables] WHERE [object_id] = OBJECT_ID(N'[GP].[Formule]') AND OBJECTPROPERTY([object_id], 'IsUserTable') = 1)
EXEC sp_executesql N'CREATE TABLE [GP].[Formule] (
  [IDFormula] [bigint] IDENTITY (1, 1) NOT NULL,
  [Descrizione] [varchar] (150) NOT NULL,
  [Ufficiale] [bit] NULL,
  [Superata] [bit] NULL,
  CONSTRAINT [PK_Formule]
  PRIMARY KEY CLUSTERED
  (
    [IDFormula] ASC
  )
  WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE)
  ON [PRIMARY]
) ON [PRIMARY];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------- *\
|* Creating object GP.FamiglieColoriXPolveri (Table) *|
\* ------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[tables] WHERE [object_id] = OBJECT_ID(N'[GP].[FamiglieColoriXPolveri]') AND OBJECTPROPERTY([object_id], 'IsUserTable') = 1)
EXEC sp_executesql N'CREATE TABLE [GP].[FamiglieColoriXPolveri] (
  [IDPolvere] [int] NOT NULL,
  [Famiglie] [varchar] (200) NULL,
  [Applicazione] [varchar] (50) NULL,
  CONSTRAINT [PK_FamiglieColoriXPolveri]
  PRIMARY KEY CLUSTERED
  (
    [IDPolvere] ASC
  )
  WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE)
  ON [PRIMARY]
) ON [PRIMARY];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------- *\
|* Creating object GP.QuickFiltri (Table) *|
\* -------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[tables] WHERE [object_id] = OBJECT_ID(N'[GP].[QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsUserTable') = 1)
EXEC sp_executesql N'CREATE TABLE [GP].[QuickFiltri] (
  [IDFiltro] [int] IDENTITY (1, 1) NOT NULL,
  [Applicazione] [char] (5) NULL,
  [Descrizione] [varchar] (150) NOT NULL,
  [DateFrom] [datetime] NULL,
  [DateTo] [datetime] NULL,
) ON [PRIMARY];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* --------------------------------------------------------------------------------- *\
|* Creating object GP.FK_FamiglieColoriXPolveri_AnagPolveri (Foreign Key Constraint) *|
\* --------------------------------------------------------------------------------- */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N'[GP].[FK_FamiglieColoriXPolveri_AnagPolveri]') AND OBJECTPROPERTY([object_id], 'IsForeignKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[FamiglieColoriXPolveri]
WITH CHECK 
ADD CONSTRAINT [FK_FamiglieColoriXPolveri_AnagPolveri]
FOREIGN KEY ([IDPolvere])
 REFERENCES [GP].[AnagPolveri]
            ([IDPolvere])
ON DELETE NO ACTION
ON UPDATE NO ACTION;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

EXEC sp_executesql N'ALTER TABLE [GP].[FamiglieColoriXPolveri] CHECK CONSTRAINT [FK_FamiglieColoriXPolveri_AnagPolveri];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ----------------------------------------------- *\
|* Creating object GP.Lotti.DataControllo (Column) *|
\* ----------------------------------------------- */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[columns] WHERE [name] = 'DataControllo' AND [object_id] = OBJECT_ID('[GP].[Lotti]'))
EXEC sp_executesql N'ALTER TABLE [GP].[Lotti]
ADD [DataControllo] [datetime] NULL;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------- *\
|* Creating object GP.Lotti.Tipo (Column) *|
\* -------------------------------------- */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[columns] WHERE [name] = 'Tipo' AND [object_id] = OBJECT_ID('[GP].[Lotti]'))
EXEC sp_executesql N'ALTER TABLE [GP].[Lotti]
ADD [Tipo] [int] NULL;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------------------------- *\
|* Creating object GP.ComposizioneColori.IDFormula (Column) *|
\* -------------------------------------------------------- */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[columns] WHERE [name] = 'IDFormula' AND [object_id] = OBJECT_ID('[GP].[ComposizioneColori]'))
EXEC sp_executesql N'ALTER TABLE [GP].[ComposizioneColori]
ADD [IDFormula] [bigint] NULL;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------------------------- *\
|* Creating object GP.ComposizioneColori.Ufficiale (Column) *|
\* -------------------------------------------------------- */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[columns] WHERE [name] = 'Ufficiale' AND [object_id] = OBJECT_ID('[GP].[ComposizioneColori]'))
EXEC sp_executesql N'ALTER TABLE [GP].[ComposizioneColori]
ADD [Ufficiale] [bit] NULL;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------------- *\
|* Creating object GP.ComposizioneColori.Superata (Column) *|
\* ------------------------------------------------------- */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[columns] WHERE [name] = 'Superata' AND [object_id] = OBJECT_ID('[GP].[ComposizioneColori]'))
EXEC sp_executesql N'ALTER TABLE [GP].[ComposizioneColori]
ADD [Superata] [bit] NULL;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ---------------------------------------------------------- *\
|* Creating object GP.PK_QuickFiltri (Primary Key Constraint) *|
\* ---------------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[key_constraints] WHERE [object_id] = OBJECT_ID(N'[GP].[PK_QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsPrimaryKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltri]
ADD CONSTRAINT [PK_QuickFiltri]
PRIMARY KEY CLUSTERED
(
  [IDFiltro] ASC
)
WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE, ONLINE = OFF)
ON [PRIMARY];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------------------------------- *\
|* Creating object GP.FK_ComposizioneColori_Formule (Foreign Key Constraint) *|
\* ------------------------------------------------------------------------- */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N'[GP].[FK_ComposizioneColori_Formule]') AND OBJECTPROPERTY([object_id], 'IsForeignKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[ComposizioneColori]
WITH CHECK 
ADD CONSTRAINT [FK_ComposizioneColori_Formule]
FOREIGN KEY ([IDFormula])
 REFERENCES [GP].[Formule]
            ([IDFormula])
ON DELETE NO ACTION
ON UPDATE NO ACTION;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

EXEC sp_executesql N'ALTER TABLE [GP].[ComposizioneColori] CHECK CONSTRAINT [FK_ComposizioneColori_Formule];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ----------------------------------------------------- *\
|* Creating object GP.QuickReportInfo (Stored Procedure) *|
\* ----------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[procedures] WHERE [object_id] = OBJECT_ID(N'[GP].[QuickReportInfo]') AND [type] = 'P')
EXEC sp_executesql N'
-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:	Quick report Direzionale, contiene informazioni veloci sulle quantità di polvere consumate...
-- ============================================
CREATE PROCEDURE [GP].[QuickReportInfo]
	@DatamovimentoFrom          DateTime     = NULL,
	@DatamovimentoTo            DateTime     = NULL,
	@Impianti                   Varchar(200) = NULL,
	@DescrizioneImpianti        Varchar(400) = NULL,
	@Applicazione               Char(1)      = NULL,
	@Famiglia                   Varchar(20)  = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    /* calcolo dei totali */
	SELECT
	sum(GP.Movimenti.Quantita) as Kg, count(GP.Movimenti.quantita) as N
	FROM GP.Movimenti
	INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
	INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale
	INNER JOIN GP.composizionecolori ON GP.composizionecolori.idpolvere = GP.movimenti.idpolvere
	INNER JOIN GP.FamiglieColoriXPolveri ON GP.Movimenti.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
	where GP.causalimagazzino.codcausale = ''S''
		  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
		  and ((@Impianti is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianti, '',''))))
		  and ((@Applicazione is null) OR (GP.composizionecolori.Applicazione = @Applicazione))
		  and ((@Famiglia is null) OR (@Famiglia IN (select param from GP.fn_Parametri(GP.FamiglieColoriXPolveri.Famiglie, '',''))))
		  
		  
    /* calcolo della quota parte */	
    declare @columns as varchar(max)
    	
    select @columns = coalesce(@columns + '','','''') + quotename(Impianto) 
    from (
       select distinct Impianto from gp.impianti
       where IDImpianto IN (select param from GP.fn_Parametri(@Impianti, '','')) 
    ) as B 
    order by B.impianto
    
    declare @sql as varchar(max)
    declare @sql1 as varchar(max)
    declare @from as varchar(25)
    declare @to as varchar(25)
    
    set @from = CONVERT(VARCHAR, @DatamovimentoFrom, 120)
    set @to = CONVERT(VARCHAR, @DatamovimentoTo, 120)
    
    /* calcolo la quota parte dei kg. */
    if ((@DatamovimentoFrom is null) and (@DatamovimentoTo is null))
     begin
       set @sql = ''
	   select * from 
			(
				SELECT
				GP.Movimenti.Quantita, GP.Impianti.Impianto
				FROM GP.Movimenti 
				INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
				INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 
				INNER JOIN GP.composizionecolori ON GP.composizionecolori.idpolvere = GP.movimenti.idpolvere
				INNER JOIN GP.FamiglieColoriXPolveri ON GP.Movimenti.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
				where GP.causalimagazzino.codcausale = ''''S''''
					  and (('''''' + @Applicazione + '''''' is null) OR (GP.composizionecolori.Applicazione = '''''' + @Applicazione + ''''''))
					  and (('''''' + @Famiglia + '''''' is null) OR ('''''' + @Famiglia + '''''' IN (select param from GP.fn_Parametri(GP.FamiglieColoriXPolveri.Famiglie, '''''' + '','' + ''''''))))
			) DataTable
			PIVOT
			(
			   sum(quantita)
			   FOR Impianto IN ('' + @columns + '')
			) PivotTable''
     end
    else
     begin
		set @sql = ''
		select * from 
			(
				SELECT
				GP.Movimenti.Quantita, GP.Impianti.Impianto
				FROM GP.Movimenti 
				INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
				INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 
				INNER JOIN GP.composizionecolori ON GP.composizionecolori.idpolvere = GP.movimenti.idpolvere
				INNER JOIN GP.FamiglieColoriXPolveri ON GP.Movimenti.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
				where GP.causalimagazzino.codcausale = ''''S'''' 
					  and (GP.Movimenti.DataMovimento between '''''' + @from + '''''' and '''''' + @to + '''''')
					  and (('''''' + @Applicazione + '''''' is null) OR (GP.composizionecolori.Applicazione = '''''' + @Applicazione + ''''''))
					  and (('''''' + @Famiglia + '''''' is null) OR ('''''' + @Famiglia + '''''' IN (select param from GP.fn_Parametri(GP.FamiglieColoriXPolveri.Famiglie, '''''' + '','' + ''''''))))
			) DataTable
			PIVOT
			(
			   sum(quantita)
			   FOR Impianto IN ('' + @columns + '')
			) PivotTable''
	 end
	  
	/* calcolo la quota parte del numero di prelievi */
    if ((@DatamovimentoFrom is null) and (@DatamovimentoTo is null))
     begin
       set @sql1 = ''
	   select * from 
			(
				SELECT
				GP.Movimenti.Quantita, GP.Impianti.Impianto
				FROM GP.Movimenti 
				INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
				INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 
				INNER JOIN GP.composizionecolori ON GP.composizionecolori.idpolvere = GP.movimenti.idpolvere
				INNER JOIN GP.FamiglieColoriXPolveri ON GP.Movimenti.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
				where GP.causalimagazzino.codcausale = ''''S''''
					  and (('''''' + @Applicazione + '''''' is null) OR (GP.composizionecolori.Applicazione = '''''' + @Applicazione + ''''''))
					  and (('''''' + @Famiglia + '''''' is null) OR ('''''' + @Famiglia + '''''' IN (select param from GP.fn_Parametri(GP.FamiglieColoriXPolveri.Famiglie, '''''' + '','' + ''''''))))
			) DataTable
			PIVOT
			(
			   count(quantita)
			   FOR Impianto IN ('' + @columns + '')
			) PivotTable''
     end
    else
     begin
		set @sql1 = ''
		select * from 
			(
				SELECT
				GP.Movimenti.Quantita, GP.Impianti.Impianto
				FROM GP.Movimenti 
				INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
				INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 
				INNER JOIN GP.composizionecolori ON GP.composizionecolori.idpolvere = GP.movimenti.idpolvere
				INNER JOIN GP.FamiglieColoriXPolveri ON GP.Movimenti.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
				where GP.causalimagazzino.codcausale = ''''S'''' 
					  and (GP.Movimenti.DataMovimento between '''''' + @from + '''''' and '''''' + @to + '''''')
					  and (('''''' + @Applicazione + '''''' is null) OR (GP.composizionecolori.Applicazione = '''''' + @Applicazione + ''''''))
					  and (('''''' + @Famiglia + '''''' is null) OR ('''''' + @Famiglia + '''''' IN (select param from GP.fn_Parametri(GP.FamiglieColoriXPolveri.Famiglie, '''''' + '','' + ''''''))))
			) DataTable
			PIVOT
			(
			   count(quantita)
			   FOR Impianto IN ('' + @columns + '')
			) PivotTable''
	 end
	 
	 	  
	print @sql
	print @sql1
	print @from
	print @to
	
	exec(@sql)
	exec(@sql1)
		
END

'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------------------------- *\
|* Creating object GP.ReportDirezionale2 (Stored Procedure) *|
\* -------------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[procedures] WHERE [object_id] = OBJECT_ID(N'[GP].[ReportDirezionale2]') AND [type] = 'P')
EXEC sp_executesql N'-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:	Carica i dati per il report direzionale ordinati per ''Famiglia'', ''ColoriAbbinati''...
-- =============================================
CREATE PROCEDURE [GP].[ReportDirezionale2] 
	@DatamovimentoFrom          DateTime     = NULL,
	@DatamovimentoTo            DateTime     = NULL,
	@CodicePolvere              Varchar(100) = NULL,
	@Famiglia                   Varchar(100) = NULL,
	@Impianto                   Varchar(100) = NULL,
	@Applicazione               Char(10)     = NULL,
	@ColoriAbbinati             Varchar(200) = NULL,
	@Fornitore                  Varchar(50) = NULL,
	@CodLotto                   Varchar(25) = NULL,
	@SortOrder                  tinyint = NULL,
	@Tipo                       tinyint = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	WITH cteColoriAbbinati(CodicePolvere, CodiceColore) AS 
	(
		SELECT DISTINCT A.CodicePolvere, C.CodiceColore
		FROM GP.AnagPolveri as A
		INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere 
		INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
		INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
	),
	cteFamiglia(CodicePolvere, descrizione, IDFamiglia) AS 
	(
		SELECT DISTINCT A.CodicePolvere, F.descrizione, F.IDFamiglia
		FROM GP.AnagPolveri as A
		INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere 
		INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
		INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
	),
	cteApplicazioni(CodicePolvere, Applicazione) AS 
	(
		SELECT DISTINCT A.CodicePolvere, CC.Applicazione
		FROM GP.AnagPolveri as A
		INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere 
	)
	SELECT
	Stuff(( select '','' + Sub.Applicazione AS [text()] from cteApplicazioni as Sub where Sub.CodicePolvere = cteApplicazioni.CodicePolvere for xml path('''')), 1, 1, '''') as ''Applicazione''
	,P1.CodicePolvere
	,Stuff(( select '','' + Sub.Descrizione AS [text()] from cteFamiglia as Sub where Sub.CodicePolvere = cteFamiglia.CodicePolvere for xml path('''')), 1, 1, '''') as ''Famiglia''
	,P1.IDPolvere, P1.Descrizione, P1.Note
	,GP.AnagFornitori.Descrizione AS Fornitore
	,Stuff(( select '','' + Sub.CodiceColore AS [text()] from cteColoriAbbinati as Sub where Sub.CodicePolvere = cteColoriAbbinati.CodicePolvere order by Sub.CodiceColore for xml path('''')), 1, 1, '''') as ''ColoriAbbinati''
	,(isnull(CaricoJ, 0.00) - isnull(ScaricoJ, 0.00)) as ''Giacenza''
	,(isnull(ValoreCaricoJ, 0.00) - isnull(ValoreScaricoJ, 0.00)) as ''Valore''
    ,isnull(CaricoJ, 0.00) as Carico
    ,isnull(ScaricoJ, 0.00) as Scarico

    ,isnull(TPU1.AAA, 0.00) as ''Giacenza Att.''
	,TPU.PrezzoUnitario
	
	,P1.ScortaMinima
    ,isnull(SC.[RAL1], 0.00) as Cons_RAL1
    ,isnull(ST.[RAL1], 0.00) as NP_RAL1  
    ,isnull(SCUP.[RAL1], 0.00) as VAL_RAL1   
    
    ,isnull(SC.[RAL2], 0.00) as Cons_RAL2
    ,isnull(ST.[RAL2], 0.00) as NP_RAL2   
    ,isnull(SCUP.[RAL2], 0.00) as VAL_RAL2   
    
    ,isnull(SC.[Verticale], 0.00) as Cons_Verticale
    ,isnull(ST.[Verticale], 0.00) as NP_Verticale
    ,isnull(SCUP.[Verticale], 0.00) as VAL_Verticale
    
    ,isnull(SC.[Sublimato], 0.00) as Cons_Sublimato
    ,isnull(ST.[Sublimato], 0.00) as NP_Sublimato   
    ,isnull(SCUP.[Sublimato], 0.00) as VAL_Sublimato   
    
    ,isnull(SC.[Colorwood Grande], 0.00) as Cons_ColorwoodGrande
    ,isnull(ST.[Colorwood Grande], 0.00) as NP_ColorwoodGrande   
    ,isnull(SCUP.[Colorwood Grande], 0.00) as VAL_ColorwoodGrande   
    
    ,isnull(SC.[Ferro], 0.00) as Cons_Ferro
    ,isnull(ST.[Ferro], 0.00) as NP_Ferro   
    ,isnull(SCUP.[Ferro], 0.00) as VAL_Ferro   
    
    ,isnull(SC.[Allineamento inventario], 0.00) as Cons_AllineamentoInventario
    ,isnull(ST.[Allineamento inventario], 0.00) as NP_AllineamentoInventario   
    ,isnull(SCUP.[Allineamento inventario], 0.00) as VAL_AllineamentoInventario   
    
    ,isnull(SC.[Reso], 0.00) as Cons_Reso
    ,isnull(ST.[Reso], 0.00) as NP_Reso   
    ,isnull(SCUP.[Reso], 0.00) as VAL_Reso   
    
    ,isnull(SC.[Venduto], 0.00) as Cons_Venduto
    ,isnull(ST.[Venduto], 0.00) as NP_Venduto   
    ,isnull(SCUP.[Venduto], 0.00) as VAL_Venduto   
    
    ,isnull(SC.[Smaltito], 0.00) as Cons_Smaltito
    ,isnull(ST.[Smaltito], 0.00) as NP_Smaltito   
    ,isnull(SCUP.[Smaltito], 0.00) as VAL_Smaltito   
    
    ,isnull(SC.[Colorwood Piccolo], 0.00) as Cons_ColorwoodPiccolo
    ,isnull(ST.[Colorwood Piccolo], 0.00) as NP_ColorwoodPiccolo   
    ,isnull(SCUP.[Colorwood Piccolo], 0.00) as VAL_ColorwoodPiccolo   
    
    ,isnull(SC.[Inventario], 0.00) as Cons_Correzione
    ,isnull(ST.[Inventario], 0.00) as NP_Correzione
    ,isnull(SCUP.[Inventario], 0.00) as VAL_Correzione
	
	
	FROM GP.AnagPolveri as P1 with (nolock)
	INNER JOIN GP.AnagFornitori ON P1.IDFornitore = GP.AnagFornitori.IDFornitore
	inner join GP.FamiglieColoriXPolveri on P1.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
	left outer join cteFamiglia on cteFamiglia.codicepolvere = p1.codicepolvere
	left outer join cteColoriabbinati on cteColoriAbbinati.codicepolvere = p1.codicepolvere
	left outer join cteApplicazioni on cteApplicazioni.codicepolvere = p1.codicepolvere
	left outer join (
		select gp.movimenti.idpolvere, sum(Quantita) as CaricoJ, sum(gp.lotti.valore) as ValoreCaricoJ
		from gp.movimenti
		inner join gp.causalimagazzino on gp.movimenti.idcausale = gp.causalimagazzino.idcausale 
		INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto	
		LEFT OUTER JOIN GP.CausaliScarico ON GP.Movimenti.IDCausaleScarico = GP.CausaliScarico.IDCausaleScarico
		where gp.causalimagazzino.codcausale = ''C'' 
		and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
		and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		--and (@Impianto is null)
		and ((@Impianto is null) OR (GP.CausaliScarico.CodCausaleScarico = ''I''))		 
		group by gp.movimenti.idpolvere
	) as TC on P1.idpolvere = TC.idpolvere
	left outer join (
		select gp.movimenti.idpolvere, sum(Quantita) as ScaricoJ, sum(gp.lotti.valore) as ValoreScaricoJ
		from gp.movimenti
		inner join gp.causalimagazzino on gp.movimenti.idcausale = gp.causalimagazzino.idcausale 
		INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
		INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto
		where gp.causalimagazzino.codcausale = ''S'' 
		and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
		and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
		and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		group by gp.movimenti.idpolvere
	) as TS on P1.idpolvere = TS.idpolvere
	
	outer apply (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Impianti.Impianto,sum(GP.Movimenti.Quantita * tpu.PrezzoUnitario) as Valorizzazione
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto   
            outer apply (
            select top 1 GPLT.prezzounitario
                from gp.lotti as GPLT
                where gplt.idpolvere = GP.Movimenti.IDPolvere
                order by GPLT.dataddt desc
            ) as TPU    
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
                  and Movimenti.IDPolvere=P1.IDPolvere
            group by GP.Movimenti.IDPolvere, GP.Impianti.Impianto
        ) DataTable
        PIVOT
        (
           sum(valorizzazione)
           FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
                                    [Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
        ) PivotTable
        
    ) as SCUP --
	
	outer apply (
	select top 1 GPLT.prezzounitario
		from gp.lotti as GPLT
		where gplt.idpolvere = p1.idpolvere
		order by GPLT.dataddt desc
	) as TPU 	
	outer apply (
		select sum(GPLT.Giacenza) as AAA 
		from gp.lotti as GPLT
		where gplt.idpolvere = p1.idpolvere
	) as TPU1 
	left outer join (
		select * from 
		(
			SELECT
			GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
			FROM GP.Movimenti 
			INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
			INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 	
		    INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto		
			where GP.causalimagazzino.codcausale = ''S'' 
		          and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
		          and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
		          and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		) DataTable
		PIVOT
		(
		   sum(quantita)
		   FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
									[Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
		) PivotTable
	) as SC on P1.idpolvere = SC.idpolvere
	left outer join (
		select * from 
		(
			SELECT
			GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
			FROM GP.Movimenti 
			INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
			INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 
		    INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto			
			where GP.causalimagazzino.codcausale = ''S'' 
			      and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
			      and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
		          and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		) DataTable
		PIVOT
		(
		   count(quantita)
		   FOR impianto IN ([RAL1],
		                    [RAL2],
		                    [Verticale],
		                    [Sublimato],
		                    [Colorwood Grande],
							[Ferro],
							[Allineamento inventario],
							[Reso],
							[Venduto],
							[Smaltito],
							[Colorwood Piccolo],
							[Inventario])
		) PivotTable
	) as ST on P1.idpolvere = ST.idpolvere
	where 
	  ((@CodicePolvere is null) OR (P1.CodicePolvere Like @CodicePolvere))
	  --and ((@Famiglia is null) OR (cteFamiglia.IDFamiglia IN (select param from GP.fn_Parametri(@Famiglia, '',''))))
	  and ((@Famiglia is null) OR (GP.FamiglieColoriXPolveri.Famiglie = @Famiglia))
	  and ((@Applicazione is null) OR (GP.FamiglieColoriXPolveri.Applicazione = @Applicazione))
	  --and ((@Applicazione is null) OR (cteApplicazioni.Applicazione Like @Applicazione))
	  --and ((@ColoriAbbinati is null) OR (cteColoriAbbinati.CodiceColore Like @ColoriAbbinati))
	  and ((@ColoriAbbinati is null) OR (@ColoriAbbinati  Like cteColoriAbbinati.CodiceColore  ))
	  and ((@Fornitore is null) OR (GP.AnagFornitori.Descrizione like @Fornitore))
	  and ((@Tipo is null) OR (P1.IsCodiceCustom = @Tipo))
	group by
	P1.CodicePolvere, P1.IDPolvere, P1.Descrizione, P1.ScortaMinima, P1.Note
	,GP.AnagFornitori.Descrizione
	,TC.CaricoJ, TS.ScaricoJ
	,TC.ValoreCaricoJ, TS.ValoreScaricoJ
	,TPU.PrezzoUnitario, TPU1.AAA
	,SC.[RAL1],SC.[RAL2],SC.[Verticale],SC.[Sublimato],SC.[Colorwood Grande],SC.[Ferro],SC.[Allineamento inventario],SC.[Reso],SC.[Venduto],SC.[Smaltito],SC.[Colorwood Piccolo], SC.[Inventario]
    ,ST.[RAL1],ST.[RAL2],ST.[Verticale],ST.[Sublimato],ST.[Colorwood Grande],ST.[Ferro],ST.[Allineamento inventario],ST.[Reso],ST.[Venduto],ST.[Smaltito],ST.[Colorwood Piccolo], ST.[Inventario]
    ,SCUP.[RAL1],SCUP.[RAL2],SCUP.[Verticale],SCUP.[Sublimato],SCUP.[Colorwood Grande],SCUP.[Ferro],SCUP.[Allineamento inventario],SCUP.[Reso],SCUP.[Venduto],SCUP.[Smaltito],SCUP.[Colorwood Piccolo],SCUP.[Inventario]
	,cteFamiglia.codicepolvere
	,cteColoriAbbinati.codicepolvere
	,cteApplicazioni.CodicePolvere
END
'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* -------------------------------------------------------- *\
|* Creating object GP.QuickReportDetails (Stored Procedure) *|
\* -------------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[procedures] WHERE [object_id] = OBJECT_ID(N'[GP].[QuickReportDetails]') AND [type] = 'P')
EXEC sp_executesql N'-- =============================================
-- Author:        Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:    Carica i dati relativi al dettaglio del cosidetto quick report...
-- =============================================
CREATE PROCEDURE [GP].[QuickReportDetails]
    @DatamovimentoFrom          DateTime     = NULL,
    @DatamovimentoTo            DateTime     = NULL,
    @Famiglia                   Varchar(100) = NULL,
    @Impianto                   Varchar(100) = NULL,
    @Applicazione               Char(1)      = NULL

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    with cteFamiglia(IDPolvere, descrizione, IDFamiglia) AS
    (
        SELECT DISTINCT A.IDPolvere, F.descrizione, F.IDFamiglia
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
        INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
        INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
    ),
    cteApplicazioni(IDPolvere, Applicazione) AS
    (
        SELECT DISTINCT A.IDPolvere, CC.Applicazione
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
    )
    select
    Applicazione,
    Famiglia,
    sum(TotKg) as TotKG
    ,sum(NTot) as TotNP
    ,sum(Cons_RAL1) as Cons_RAL1
    ,sum(NP_RAL1) as NP_RAL1
    ,sum(Cons_RAL2) as Cons_RAL2
    ,sum(NP_RAL2) as NP_RAL2
    ,sum(Cons_Verticale) as Cons_Verticale
    ,sum(NP_Verticale) as NP_Verticale
    ,sum(Cons_Sublimato) as Cons_Sublimato
    ,sum(NP_Sublimato) as NP_Sublimato
    ,sum(Cons_ColorwoodGrande) as Cons_ColorwoodGrande
    ,sum(NP_ColorwoodGrande) as NP_ColorwoodGrande
    ,sum(Cons_Ferro) as Cons_Ferro
    ,sum(NP_Ferro) as NP_Ferro
    ,sum(Cons_AllineamentoInventario) as Cons_AllineamentoInventario
    ,sum(NP_AllineamentoInventatio) as NP_AllineamentoInventatio
    ,sum(Cons_Reso) as Cons_Reso
    ,sum(NP_Reso) as NP_Reso
    ,sum(Cons_Venduto) as Cons_Venduto
    ,sum(NP_Venduto) as NP_Venduto
    ,sum(Cons_Smaltito) as Cons_Smaltito
    ,sum(NP_Smaltito) as NP_Smaltito
    ,sum(Cons_ColorwoodPiccolo) as Cons_ColorwoodPiccolo
    ,sum(NP_ColorwoodPiccolo) as NP_ColorwoodPiccolo
    ,sum(Cons_Correzione) as Cons_Correzione
    ,sum(NP_Correzione) as NP_Correzione
   
    from (
   
    SELECT
   
    Stuff(( select '','' + Sub.Applicazione AS [text()] from cteApplicazioni as Sub where Sub.IDPolvere = cteApplicazioni.IDPolvere for xml path('''')), 1, 1, '''') as Applicazione 
    ,Stuff(( select '','' + Sub.Descrizione AS [text()] from cteFamiglia as Sub where Sub.IDPolvere = cteFamiglia.IDPolvere for xml path('''')), 1, 1, '''') as Famiglia  
   
    ,isnull(SC.[RAL1], 0.00) as Cons_RAL1
    ,isnull(ST.[RAL1], 0.00) as NP_RAL1   
    ,isnull(SC.[RAL2], 0.00) as Cons_RAL2
    ,isnull(ST.[RAL2], 0.00) as NP_RAL2   
    ,isnull(SC.[Verticale], 0.00) as Cons_Verticale
    ,isnull(ST.[Verticale], 0.00) as NP_Verticale
    ,isnull(SC.[Sublimato], 0.00) as Cons_Sublimato
    ,isnull(ST.[Sublimato], 0.00) as NP_Sublimato   
    ,isnull(SC.[Colorwood Grande], 0.00) as Cons_ColorwoodGrande
    ,isnull(ST.[Colorwood Grande], 0.00) as NP_ColorwoodGrande   
    ,isnull(SC.[Ferro], 0.00) as Cons_Ferro
    ,isnull(ST.[Ferro], 0.00) as NP_Ferro   
    ,isnull(SC.[Allineamento inventario], 0.00) as Cons_AllineamentoInventario
    ,isnull(ST.[Allineamento inventario], 0.00) as NP_AllineamentoInventatio   
    ,isnull(SC.[Reso], 0.00) as Cons_Reso
    ,isnull(ST.[Reso], 0.00) as NP_Reso   
    ,isnull(SC.[Venduto], 0.00) as Cons_Venduto
    ,isnull(ST.[Venduto], 0.00) as NP_Venduto   
    ,isnull(SC.[Smaltito], 0.00) as Cons_Smaltito
    ,isnull(ST.[Smaltito], 0.00) as NP_Smaltito   
    ,isnull(SC.[Colorwood Piccolo], 0.00) as Cons_ColorwoodPiccolo
    ,isnull(ST.[Colorwood Piccolo], 0.00) as NP_ColorwoodPiccolo   
    ,isnull(SC.[Inventario], 0.00) as Cons_Correzione
    ,isnull(ST.[Inventario], 0.00) as NP_Correzione
    ,isnull(TPU.TotScarico, 0.00) as TotKg
    ,isnull(TPU1.TotPrelievi, 0.00) as NTot
   
    FROM GP.Movimenti as P1 with (nolock)
    inner join GP.FamiglieColoriXPolveri on P1.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
    left outer join cteFamiglia on cteFamiglia.IdPolvere = p1.IDPolvere
    left outer join cteApplicazioni on cteApplicazioni.IDPolvere = p1.IDPolvere
    left outer join (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto      
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
        ) DataTable
        PIVOT
        (
           sum(quantita)
           FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
                                    [Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
        ) PivotTable
    ) as SC on P1.idpolvere = SC.idpolvere
    left outer join (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto          
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
        ) DataTable
        PIVOT
        (
           count(quantita)
           FOR impianto IN ([RAL1],
                            [RAL2],
                            [Verticale],
                            [Sublimato],
                            [Colorwood Grande],
                            [Ferro],
                            [Allineamento inventario],
                            [Reso],
                            [Venduto],
                            [Smaltito],
                            [Colorwood Piccolo],
                            [Inventario])
        ) PivotTable
    ) as ST on P1.idpolvere = ST.idpolvere
    outer apply (
        select SUM(quantita) as TotScarico
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = ''S''
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
    ) as TPU
    outer apply (
        select COUNT(quantita) as TotPrelievi
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = ''S''
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
    ) as TPU1   
    where
      --((@Famiglia is null) OR (cteFamiglia.IDFamiglia IN (select param from GP.fn_Parametri(@Famiglia, '',''))))
      ((@Famiglia is null) OR (GP.FamiglieColoriXPolveri.Famiglie = @Famiglia))
      and ((@Applicazione is null) OR (cteApplicazioni.Applicazione Like @Applicazione))
       
    group by
    P1.IDPolvere
    ,SC.[RAL1],SC.[RAL2],SC.[Verticale],SC.[Sublimato],SC.[Colorwood Grande],SC.[Ferro],SC.[Allineamento inventario],SC.[Reso],SC.[Venduto],SC.[Smaltito],SC.[Colorwood Piccolo], SC.[Inventario]
    ,ST.[RAL1],ST.[RAL2],ST.[Verticale],ST.[Sublimato],ST.[Colorwood Grande],ST.[Ferro],ST.[Allineamento inventario],ST.[Reso],ST.[Venduto],ST.[Smaltito],ST.[Colorwood Piccolo], ST.[Inventario]
    ,cteFamiglia.IDPolvere
    ,cteApplicazioni.IDPolvere
    ,TPU.TotScarico
    ,TPU1.TotPrelievi
) as T
group by Applicazione, Famiglia

END'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ----------------------------------------------------- *\
|* Creating object GP.QuickReport_OLD (Stored Procedure) *|
\* ----------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[procedures] WHERE [object_id] = OBJECT_ID(N'[GP].[QuickReport_OLD]') AND [type] = 'P')
EXEC sp_executesql N'-- =============================================
-- Author:        Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:    Carica i dati relativi del cosidetto quick report...
-- =============================================
CREATE PROCEDURE [GP].[QuickReport_OLD]
    @DatamovimentoFrom          DateTime     = NULL,
    @DatamovimentoTo            DateTime     = NULL,
    @Famiglia                   Varchar(100) = NULL,
    @Impianto                   Varchar(100) = NULL,
    @Applicazione               Char(1)      = NULL

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    with cteFamiglia(IDPolvere, descrizione, IDFamiglia) AS
    (
        SELECT DISTINCT A.IDPolvere, F.descrizione, F.IDFamiglia
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
        INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
        INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
    ),
    cteApplicazioni(IDPolvere, Applicazione) AS
    (
        SELECT DISTINCT A.IDPolvere, CC.Applicazione
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
    )
    select
    sum(TotKg) as TotKG
    ,sum(NTot) as TotNP
    ,sum(Cons_RAL1) as Cons_RAL1
    ,sum(NP_RAL1) as NP_RAL1
    ,sum(Cons_RAL2) as Cons_RAL2
    ,sum(NP_RAL2) as NP_RAL2
    ,sum(Cons_Verticale) as Cons_Verticale
    ,sum(NP_Verticale) as NP_Verticale
    ,sum(Cons_Sublimato) as Cons_Sublimato
    ,sum(NP_Sublimato) as NP_Sublimato
    ,sum(Cons_ColorwoodGrande) as Cons_ColorwoodGrande
    ,sum(NP_ColorwoodGrande) as NP_ColorwoodGrande
    ,sum(Cons_Ferro) as Cons_Ferro
    ,sum(NP_Ferro) as NP_Ferro
    ,sum(Cons_AllineamentoInventario) as Cons_AllineamentoInventario
    ,sum(NP_AllineamentoInventatio) as NP_AllineamentoInventatio
    ,sum(Cons_Reso) as Cons_Reso
    ,sum(NP_Reso) as NP_Reso
    ,sum(Cons_Venduto) as Cons_Venduto
    ,sum(NP_Venduto) as NP_Venduto
    ,sum(Cons_Smaltito) as Cons_Smaltito
    ,sum(NP_Smaltito) as NP_Smaltito
    ,sum(Cons_ColorwoodPiccolo) as Cons_ColorwoodPiccolo
    ,sum(NP_ColorwoodPiccolo) as NP_ColorwoodPiccolo
    ,sum(Cons_Correzione) as Cons_Correzione
    ,sum(NP_Correzione) as NP_Correzione
    
    from (
   
    SELECT
     isnull(SC.[RAL1], 0.00) as Cons_RAL1
    ,isnull(ST.[RAL1], 0.00) as NP_RAL1   
    ,isnull(SC.[RAL2], 0.00) as Cons_RAL2
    ,isnull(ST.[RAL2], 0.00) as NP_RAL2   
    ,isnull(SC.[Verticale], 0.00) as Cons_Verticale
    ,isnull(ST.[Verticale], 0.00) as NP_Verticale
    ,isnull(SC.[Sublimato], 0.00) as Cons_Sublimato
    ,isnull(ST.[Sublimato], 0.00) as NP_Sublimato   
    ,isnull(SC.[Colorwood Grande], 0.00) as Cons_ColorwoodGrande
    ,isnull(ST.[Colorwood Grande], 0.00) as NP_ColorwoodGrande   
    ,isnull(SC.[Ferro], 0.00) as Cons_Ferro
    ,isnull(ST.[Ferro], 0.00) as NP_Ferro   
    ,isnull(SC.[Allineamento inventario], 0.00) as Cons_AllineamentoInventario
    ,isnull(ST.[Allineamento inventario], 0.00) as NP_AllineamentoInventatio   
    ,isnull(SC.[Reso], 0.00) as Cons_Reso
    ,isnull(ST.[Reso], 0.00) as NP_Reso   
    ,isnull(SC.[Venduto], 0.00) as Cons_Venduto
    ,isnull(ST.[Venduto], 0.00) as NP_Venduto   
    ,isnull(SC.[Smaltito], 0.00) as Cons_Smaltito
    ,isnull(ST.[Smaltito], 0.00) as NP_Smaltito   
    ,isnull(SC.[Colorwood Piccolo], 0.00) as Cons_ColorwoodPiccolo
    ,isnull(ST.[Colorwood Piccolo], 0.00) as NP_ColorwoodPiccolo   
    ,isnull(SC.[Inventario], 0.00) as Cons_Correzione
    ,isnull(ST.[Inventario], 0.00) as NP_Correzione
    ,isnull(TPU.TotScarico, 0.00) as TotKg
    ,isnull(TPU1.TotPrelievi, 0.00) as NTot
       
    FROM GP.Movimenti as P1 with (nolock)
    inner join GP.FamiglieColoriXPolveri on P1.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
    left outer join cteFamiglia on cteFamiglia.IdPolvere = p1.IDPolvere
    left outer join cteApplicazioni on cteApplicazioni.IDPolvere = p1.IDPolvere  
    left outer join (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto      
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
        ) DataTable
        PIVOT
        (
           sum(quantita)
           FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
                                    [Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
        ) PivotTable
    ) as SC on P1.idpolvere = SC.idpolvere
    left outer join (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto          
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
        ) DataTable
        PIVOT
        (
           count(quantita)
           FOR impianto IN ([RAL1],
                            [RAL2],
                            [Verticale],
                            [Sublimato],
                            [Colorwood Grande],
                            [Ferro],
                            [Allineamento inventario],
                            [Reso],
                            [Venduto],
                            [Smaltito],
                            [Colorwood Piccolo],
                            [Inventario])
        ) PivotTable
    ) as ST on P1.idpolvere = ST.idpolvere
    outer apply (
        select SUM(quantita) as TotScarico
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = ''S''
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
    ) as TPU
    outer apply (
        select COUNT(quantita) as TotPrelievi
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = ''S''
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
    ) as TPU1   
    where
      --((@Famiglia is null) OR (cteFamiglia.IDFamiglia IN (select param from GP.fn_Parametri(@Famiglia, '',''))))
      ((@Famiglia is null) OR (GP.FamiglieColoriXPolveri.Famiglie = @Famiglia))
      and ((@Applicazione is null) OR (cteApplicazioni.Applicazione Like @Applicazione))
       
    group by
    P1.IDPolvere
    ,SC.[RAL1],SC.[RAL2],SC.[Verticale],SC.[Sublimato],SC.[Colorwood Grande],SC.[Ferro],SC.[Allineamento inventario],SC.[Reso],SC.[Venduto],SC.[Smaltito],SC.[Colorwood Piccolo], SC.[Inventario]
    ,ST.[RAL1],ST.[RAL2],ST.[Verticale],ST.[Sublimato],ST.[Colorwood Grande],ST.[Ferro],ST.[Allineamento inventario],ST.[Reso],ST.[Venduto],ST.[Smaltito],ST.[Colorwood Piccolo], ST.[Inventario]
    ,cteFamiglia.IDPolvere
    ,cteApplicazioni.IDPolvere
    ,TPU.TotScarico
    ,TPU1.TotPrelievi
) as T


END'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* --------------------------------------------------------------- *\
|* Creating object GP.QuickReportValorizzazioni (Stored Procedure) *|
\* --------------------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[procedures] WHERE [object_id] = OBJECT_ID(N'[GP].[QuickReportValorizzazioni]') AND [type] = 'P')
EXEC sp_executesql N'-- =============================================
-- Author:        Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:    Carica i dati relativi alle valorizzazioni per il cosidetto quick report...
-- =============================================
CREATE PROCEDURE [GP].[QuickReportValorizzazioni]
    @DatamovimentoFrom          DateTime     = NULL,
    @DatamovimentoTo            DateTime     = NULL,
    @Famiglia                   Varchar(100) = NULL,
    @Impianto                   Varchar(100) = NULL,
    @Applicazione               Char(1)      = NULL

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    WITH cteApplicazioni(IDPolvere, Applicazione) AS
	(
		SELECT DISTINCT A.IDPolvere, CC.Applicazione
		FROM GP.AnagPolveri as A
		INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
	)
	select 
	
	sum(isnull([RAL1], 0.00)) as V_RAL1
	,sum(isnull([RAL2], 0.00))as V_RAL2
	,sum(isnull([Verticale], 0.00))as V_Verticale
	,sum(isnull([Sublimato], 0.00))as V_Sublimato
	,sum(isnull([Colorwood Grande], 0.00))as V_ColorwoodGrande
	,sum(isnull([Ferro], 0.00))as V_Ferro
	,sum(isnull([Allineamento inventario], 0.00))as V_AllineamentoInventario
	,sum(isnull([Reso], 0.00))as V_Reso
	,sum(isnull([Venduto], 0.00))as V_Venduto
	,sum(isnull([Smaltito], 0.00))as V_Smaltito
	,sum(isnull([Colorwood Piccolo], 0.00))as V_ColorwoodPiccolo
	,sum(isnull([Inventario], 0.00))as V_Inventario
	
	from (

		select 
		   [RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande], [Ferro], 
		   [Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario] 
	    from
		(
			SELECT 
			GP.Impianti.Impianto, (sum(GP.Movimenti.Quantita) * tpu.PrezzoUnitario) as Valorizzazione
			FROM GP.Movimenti with (nolock)
			inner join GP.FamiglieColoriXPolveri on GP.Movimenti.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
			INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
			INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
			INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto 
			left outer join cteApplicazioni on cteApplicazioni.IDPolvere = GP.Movimenti.IDPolvere
		    outer apply (
			select top 1 GPLT.prezzounitario
				from gp.lotti as GPLT
				where gplt.idpolvere = GP.Movimenti.IDPolvere
				order by GPLT.dataddt desc
			) as TPU       
			where GP.causalimagazzino.codcausale = ''S''
				  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
				  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
				  and ((@Famiglia is null) OR (GP.FamiglieColoriXPolveri.Famiglie = @Famiglia))
				  and ((@Applicazione is null) OR (cteApplicazioni.Applicazione Like @Applicazione))
				  --and gp.movimenti.idpolvere = 276
		    group by GP.Movimenti.IDPolvere, GP.Impianti.Impianto, GP.Movimenti.Quantita, tpu.PrezzoUnitario		  
		) DataTable
		PIVOT
		(
		   sum(Valorizzazione)
		   FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
									[Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
		) PivotTable

	) as T
   
END'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------- *\
|* Creating object GP.QuickReport (Stored Procedure) *|
\* ------------------------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[procedures] WHERE [object_id] = OBJECT_ID(N'[GP].[QuickReport]') AND [type] = 'P')
EXEC sp_executesql N'-- =============================================
-- Author:        Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:    Carica i dati relativi del cosidetto quick report...
-- =============================================
CREATE PROCEDURE [GP].[QuickReport]
    @DatamovimentoFrom          DateTime     = NULL,
    @DatamovimentoTo            DateTime     = NULL,
    @Famiglia                   Varchar(100) = NULL,
    @Impianto                   Varchar(100) = NULL,
    @Applicazione               Char(10)     = NULL

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    with cteFamiglia(IDPolvere, descrizione, IDFamiglia) AS
    (
        SELECT DISTINCT A.IDPolvere, F.descrizione, F.IDFamiglia
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
        INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
        INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
    ),
    cteApplicazioni(IDPolvere, Applicazione) AS
    (
        SELECT DISTINCT A.IDPolvere, CC.Applicazione
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
    )
    select
     sum(TotKg) as TotKG
    ,sum(NTot) as TotNP
    ,sum(Val_Tot) as TotVAL
    
    ,sum(Cons_RAL1) as Cons_RAL1
    ,sum(NP_RAL1) as NP_RAL1
    ,sum(VAL_RAL1) as V_RAL1
    
    ,sum(Cons_RAL2) as Cons_RAL2
    ,sum(NP_RAL2) as NP_RAL2
    ,sum(VAL_RAL2) as V_RAL2
    
    ,sum(Cons_Verticale) as Cons_Verticale
    ,sum(NP_Verticale) as NP_Verticale
    ,sum(VAL_Verticale) as V_Verticale
    
    ,sum(Cons_Sublimato) as Cons_Sublimato
    ,sum(NP_Sublimato) as NP_Sublimato
    ,sum(VAL_Sublimato) as V_Sublimato
    
    ,sum(Cons_ColorwoodGrande) as Cons_ColorwoodGrande
    ,sum(NP_ColorwoodGrande) as NP_ColorwoodGrande
    ,sum(VAL_ColorwoodGrande) as V_ColorwoodGrande
    
    ,sum(Cons_Ferro) as Cons_Ferro
    ,sum(NP_Ferro) as NP_Ferro
    ,sum(VAL_Ferro) as V_Ferro
    
    ,sum(Cons_AllineamentoInventario) as Cons_AllineamentoInventario
    ,sum(NP_AllineamentoInventario) as NP_AllineamentoInventario
    ,sum(VAL_AllineamentoInventario) as V_AllineamentoInventario
    
    ,sum(Cons_Reso) as Cons_Reso
    ,sum(NP_Reso) as NP_Reso
    ,sum(VAL_Reso) as V_RESO
    
    ,sum(Cons_Venduto) as Cons_Venduto
    ,sum(NP_Venduto) as NP_Venduto
    ,sum(VAL_Venduto) as V_Venduto
    
    ,sum(Cons_Smaltito) as Cons_Smaltito
    ,sum(NP_Smaltito) as NP_Smaltito
    ,sum(VAL_Smaltito) as V_Smaltito
    
    ,sum(Cons_ColorwoodPiccolo) as Cons_ColorwoodPiccolo
    ,sum(NP_ColorwoodPiccolo) as NP_ColorwoodPiccolo
    ,sum(VAL_ColorwoodPiccolo) as V_ColorwoodPiccolo
    
    ,sum(Cons_Correzione) as Cons_Correzione
    ,sum(NP_Correzione) as NP_Correzione
    ,sum(VAL_Correzione) as V_Correzione
   
    from (
   
    SELECT
     isnull(SC.[RAL1], 0.00) as Cons_RAL1
    ,isnull(ST.[RAL1], 0.00) as NP_RAL1  
    ,isnull(SCUP.[RAL1], 0.00) as VAL_RAL1   
    
    ,isnull(SC.[RAL2], 0.00) as Cons_RAL2
    ,isnull(ST.[RAL2], 0.00) as NP_RAL2   
    ,isnull(SCUP.[RAL2], 0.00) as VAL_RAL2   
    
    ,isnull(SC.[Verticale], 0.00) as Cons_Verticale
    ,isnull(ST.[Verticale], 0.00) as NP_Verticale
    ,isnull(SCUP.[Verticale], 0.00) as VAL_Verticale
    
    ,isnull(SC.[Sublimato], 0.00) as Cons_Sublimato
    ,isnull(ST.[Sublimato], 0.00) as NP_Sublimato   
    ,isnull(SCUP.[Sublimato], 0.00) as VAL_Sublimato   
    
    ,isnull(SC.[Colorwood Grande], 0.00) as Cons_ColorwoodGrande
    ,isnull(ST.[Colorwood Grande], 0.00) as NP_ColorwoodGrande   
    ,isnull(SCUP.[Colorwood Grande], 0.00) as VAL_ColorwoodGrande   
    
    ,isnull(SC.[Ferro], 0.00) as Cons_Ferro
    ,isnull(ST.[Ferro], 0.00) as NP_Ferro   
    ,isnull(SCUP.[Ferro], 0.00) as VAL_Ferro   
    
    ,isnull(SC.[Allineamento inventario], 0.00) as Cons_AllineamentoInventario
    ,isnull(ST.[Allineamento inventario], 0.00) as NP_AllineamentoInventario   
    ,isnull(SCUP.[Allineamento inventario], 0.00) as VAL_AllineamentoInventario   
    
    ,isnull(SC.[Reso], 0.00) as Cons_Reso
    ,isnull(ST.[Reso], 0.00) as NP_Reso   
    ,isnull(SCUP.[Reso], 0.00) as VAL_Reso   
    
    ,isnull(SC.[Venduto], 0.00) as Cons_Venduto
    ,isnull(ST.[Venduto], 0.00) as NP_Venduto   
    ,isnull(SCUP.[Venduto], 0.00) as VAL_Venduto   
    
    ,isnull(SC.[Smaltito], 0.00) as Cons_Smaltito
    ,isnull(ST.[Smaltito], 0.00) as NP_Smaltito   
    ,isnull(SCUP.[Smaltito], 0.00) as VAL_Smaltito   
    
    ,isnull(SC.[Colorwood Piccolo], 0.00) as Cons_ColorwoodPiccolo
    ,isnull(ST.[Colorwood Piccolo], 0.00) as NP_ColorwoodPiccolo   
    ,isnull(SCUP.[Colorwood Piccolo], 0.00) as VAL_ColorwoodPiccolo   
    
    ,isnull(SC.[Inventario], 0.00) as Cons_Correzione
    ,isnull(ST.[Inventario], 0.00) as NP_Correzione
    ,isnull(SCUP.[Inventario], 0.00) as VAL_Correzione
    
    ,isnull(TPU.TotScarico, 0.00) as TotKg
    ,isnull(TPU1.TotPrelievi, 0.00) as NTot
    ,isnull(TPU2.TotValorizzazione, 0.00) as VAL_tot
   
    FROM GP.Movimenti as P1 with (nolock)
    inner join GP.FamiglieColoriXPolveri on P1.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
    left outer join cteFamiglia on cteFamiglia.IdPolvere = p1.IDPolvere
    left outer join cteApplicazioni on cteApplicazioni.IDPolvere = p1.IDPolvere
    outer apply (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto      
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
                  and Movimenti.IDPolvere=P1.IDPolvere
        ) DataTable
        PIVOT
        (
           sum(quantita)
           FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
                                    [Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
        ) PivotTable
        
    ) as SC -- on P1.idpolvere = SC.idpolvere
    outer apply (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Impianti.Impianto,sum(GP.Movimenti.Quantita * tpu.PrezzoUnitario) as Valorizzazione
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto   
            outer apply (
            select top 1 GPLT.prezzounitario
                from gp.lotti as GPLT
                where gplt.idpolvere = GP.Movimenti.IDPolvere
                order by GPLT.dataddt desc
            ) as TPU    
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
                  and Movimenti.IDPolvere=P1.IDPolvere
            group by GP.Movimenti.IDPolvere, GP.Impianti.Impianto
        ) DataTable
        PIVOT
        (
           sum(valorizzazione)
           FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
                                    [Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
        ) PivotTable
        
    ) as SCUP -- on P1.idpolvere = SC.idpolvere
    outer apply(
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto          
            where GP.causalimagazzino.codcausale = ''S''
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
                  and Movimenti.IDPolvere=P1.IDPolvere
        ) DataTable
        PIVOT
        (
           count(quantita)
           FOR impianto IN ([RAL1],
                            [RAL2],
                            [Verticale],
                            [Sublimato],
                            [Colorwood Grande],
                            [Ferro],
                            [Allineamento inventario],
                            [Reso],
                            [Venduto],
                            [Smaltito],
                            [Colorwood Piccolo],
                            [Inventario])
        ) PivotTable
        
        
    ) as ST --on P1.idpolvere = ST.idpolvere
    
    outer apply (
        select SUM(quantita) as TotScarico
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = ''S''
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
    ) as TPU
    outer apply (
        select COUNT(quantita) as TotPrelievi
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = ''S''
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
    ) as TPU1  
    outer apply (
        select sum(mov.Quantita * TPUV.PrezzoUnitario) as TotValorizzazione
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        outer apply (
            select top 1 GPLT.prezzounitario
                from gp.lotti as GPLT
                where gplt.idpolvere = mov.IDPolvere
                order by GPLT.dataddt desc
            ) as TPUV
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = ''S''
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, '',''))))
    ) as TPU2      
    where
      --((@Famiglia is null) OR (cteFamiglia.IDFamiglia IN (select param from GP.fn_Parametri(@Famiglia, '',''))))
      ((@Famiglia is null) OR (GP.FamiglieColoriXPolveri.Famiglie = @Famiglia))
      and ((@Applicazione is null) OR (GP.FamiglieColoriXPolveri.Applicazione = @Applicazione))
      --and ((@Applicazione is null) OR (cteApplicazioni.Applicazione Like @Applicazione))
       
    group by
    P1.IDPolvere
    ,SC.[RAL1],SC.[RAL2],SC.[Verticale],SC.[Sublimato],SC.[Colorwood Grande],SC.[Ferro],SC.[Allineamento inventario],SC.[Reso],SC.[Venduto],SC.[Smaltito],SC.[Colorwood Piccolo], SC.[Inventario]
    ,ST.[RAL1],ST.[RAL2],ST.[Verticale],ST.[Sublimato],ST.[Colorwood Grande],ST.[Ferro],ST.[Allineamento inventario],ST.[Reso],ST.[Venduto],ST.[Smaltito],ST.[Colorwood Piccolo], ST.[Inventario]
    ,SCUP.[RAL1],SCUP.[RAL2],SCUP.[Verticale],SCUP.[Sublimato],SCUP.[Colorwood Grande],SCUP.[Ferro],SCUP.[Allineamento inventario],SCUP.[Reso],SCUP.[Venduto],SCUP.[Smaltito],SCUP.[Colorwood Piccolo],SCUP.[Inventario]
    ,cteFamiglia.IDPolvere
    ,cteApplicazioni.IDPolvere
    ,TPU.TotScarico
    ,TPU1.TotPrelievi
    ,TPU2.TotValorizzazione
) as T


END
'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* --------------------------------------------- *\
|* Disabling foreign keys for table data restore *|
\* --------------------------------------------- */

BEGIN TRY

CREATE TABLE [#__schemaEngine__disabled_foreign_keys] (TableSchemaName [nvarchar](MAX), TableName [nvarchar](MAX), ForeignKeyName [nvarchar](MAX))

INSERT INTO [#__schemaEngine__disabled_foreign_keys] ([TableSchemaName], [TableName], [ForeignKeyName])
SELECT ISNULL([s].[name], '') AS [TableSchemaName], OBJECT_NAME([fk].[parent_object_id]) AS [TableName], [fk].[name] FROM [sys].[foreign_keys] [fk]
INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [fk].[parent_object_id] INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [o].[schema_id]
WHERE [fk].[is_disabled] = 0
AND [referenced_object_id] IN (OBJECT_ID('[GP].[QuickFiltri]'))

-- get the foreign keys referencing this object
DECLARE @SQL [nvarchar](MAX)
DECLARE @TableSchemaName [nvarchar](MAX)
DECLARE @TableName [nvarchar](MAX)
DECLARE @ForeignKeyName [nvarchar](MAX)

DECLARE foreignKeyCursor CURSOR READ_ONLY
FOR 
SELECT [TableSchemaName], [TableName], [ForeignKeyName] FROM [#__schemaEngine__disabled_foreign_keys]

OPEN foreignKeyCursor

FETCH NEXT FROM foreignKeyCursor INTO @TableSchemaName, @TableName, @ForeignKeyName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		
    SET @SQL = 'ALTER TABLE [' + REPLACE(@TableSchemaName, ']', ']]') + '].[' + REPLACE(@TableName, ']', ']]') + '] NOCHECK CONSTRAINT [' +  REPLACE(@ForeignKeyName, ']', ']]') + ']'
    EXEC (@SQL)
    
	END
	FETCH NEXT FROM foreignKeyCursor INTO @TableSchemaName, @TableName, @ForeignKeyName
END

CLOSE foreignKeyCursor
DEALLOCATE foreignKeyCursor
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* --------------------------------------------- *\
|* Disabling DML triggers for table data restore *|
\* --------------------------------------------- */

BEGIN TRY

CREATE TABLE [#__schemaEngine__disabled_triggers] (TableSchemaName [sysname], TableName [sysname], TriggerName [sysname])

INSERT INTO [#__schemaEngine__disabled_triggers] ([TableSchemaName], [TableName], [TriggerName])
SELECT ISNULL([s].[name], '') AS [TableSchemaName], ISNULL([o].[name], '') AS [TableName], ISNULL([t].[name], '') AS [TriggerName] FROM [sys].[triggers] [t]
INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [t].[parent_id]
INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [o].[schema_id]
WHERE [t].[parent_id] > 0 AND [t].[is_disabled] = 0
AND [t].[parent_id] IN (OBJECT_ID('[GP].[QuickFiltri]'))

-- get the foreign keys referencing this object
DECLARE @SQL [nvarchar](4000)
DECLARE @TableSchemaName [sysname]
DECLARE @TableName [sysname]
DECLARE @TriggerName [sysname]

DECLARE triggerCursor CURSOR READ_ONLY
FOR 
SELECT [TableSchemaName], [TableName], [TriggerName] FROM [#__schemaEngine__disabled_triggers]

OPEN triggerCursor

FETCH NEXT FROM triggerCursor INTO @TableSchemaName, @TableName, @TriggerName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		
    SET @SQL = 'DISABLE TRIGGER [' + REPLACE(@TableSchemaName, ']', ']]') + '].[' + REPLACE(@TriggerName, ']', ']]') + '] ON [' +  REPLACE(@TableSchemaName, ']', ']]') + '].[' + REPLACE(@TableName, ']', ']]') + ']'
    EXEC (@SQL)
    
	END
	FETCH NEXT FROM triggerCursor INTO @TableSchemaName, @TableName, @TriggerName
END

CLOSE triggerCursor
DEALLOCATE triggerCursor
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ---------------------------------------- *\
|* Restoring data to GP.QuickFiltri (Table) *|
\* ---------------------------------------- */

BEGIN TRY

SET IDENTITY_INSERT [GP].[QuickFiltri] ON;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

INSERT
  INTO [GP].[QuickFiltri] ([IDFiltro], [Applicazione])
SELECT [IDFiltro], [Applicazione]
  FROM [__SE_Storage_QuickFiltri_235147883_20130507191458];
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

SET IDENTITY_INSERT [GP].[QuickFiltri] OFF;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

DROP TABLE [__SE_Storage_QuickFiltri_235147883_20130507191458];
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ---------------------------------------------- *\
|* Enabling foreign keys after table data restore *|
\* ---------------------------------------------- */

BEGIN TRY

DECLARE @SQL [nvarchar](MAX)
DECLARE @TableSchemaName [nvarchar](MAX)
DECLARE @TableName [nvarchar](MAX)
DECLARE @ForeignKeyName [nvarchar](MAX)

DECLARE foreignKeyCursor CURSOR READ_ONLY
FOR 
SELECT [TableSchemaName], [TableName], [ForeignKeyName] FROM [#__schemaEngine__disabled_foreign_keys]

OPEN foreignKeyCursor

FETCH NEXT FROM foreignKeyCursor INTO @TableSchemaName, @TableName, @ForeignKeyName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		
    SET @SQL = 'ALTER TABLE [' + REPLACE(@TableSchemaName, ']', ']]') + '].[' + REPLACE(@TableName, ']', ']]') + '] CHECK CONSTRAINT [' +  REPLACE(@ForeignKeyName, ']', ']]') + ']'
    EXEC (@SQL)
    
	END
	FETCH NEXT FROM foreignKeyCursor INTO @TableSchemaName, @TableName, @ForeignKeyName
END

CLOSE foreignKeyCursor
DEALLOCATE foreignKeyCursor
DROP TABLE [#__schemaEngine__disabled_foreign_keys]
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ---------------------------------------------- *\
|* Enabling DML triggers after table data restore *|
\* ---------------------------------------------- */

BEGIN TRY

DECLARE @SQL [nvarchar](MAX)
DECLARE @TableSchemaName [nvarchar](MAX)
DECLARE @TableName [nvarchar](MAX)
DECLARE @TriggerName [nvarchar](MAX)

DECLARE triggerCursor CURSOR READ_ONLY
FOR 
SELECT [TableSchemaName], [TableName], [TriggerName] FROM [#__schemaEngine__disabled_triggers]

OPEN triggerCursor

FETCH NEXT FROM triggerCursor INTO @TableSchemaName, @TableName, @TriggerName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		
    SET @SQL = 'ENABLE TRIGGER [' + REPLACE(@TableSchemaName, ']', ']]') + '].[' + REPLACE(@TriggerName, ']', ']]') + '] ON [' +  REPLACE(@TableSchemaName, ']', ']]') + '].[' + REPLACE(@TableName, ']', ']]') + ']'
    EXEC (@SQL)
    
	END
	FETCH NEXT FROM triggerCursor INTO @TableSchemaName, @TableName, @TriggerName
END

CLOSE triggerCursor
DEALLOCATE triggerCursor
DROP TABLE [#__schemaEngine__disabled_triggers]
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------------------------------------ *\
|* Creating object GP.FK_QuickFiltriImpianti_QuickFiltri (Foreign Key Constraint) *|
\* ------------------------------------------------------------------------------ */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N'[GP].[FK_QuickFiltriImpianti_QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsForeignKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltriImpianti]
WITH CHECK 
ADD CONSTRAINT [FK_QuickFiltriImpianti_QuickFiltri]
FOREIGN KEY ([IDFiltro])
 REFERENCES [GP].[QuickFiltri]
            ([IDFiltro])
ON DELETE NO ACTION
ON UPDATE NO ACTION;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltriImpianti] CHECK CONSTRAINT [FK_QuickFiltriImpianti_QuickFiltri];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ------------------------------------------------------------------------------ *\
|* Creating object GP.FK_QuickFiltriFamiglie_QuickFiltri (Foreign Key Constraint) *|
\* ------------------------------------------------------------------------------ */

BEGIN TRY

IF NOT EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N'[GP].[FK_QuickFiltriFamiglie_QuickFiltri]') AND OBJECTPROPERTY([object_id], 'IsForeignKey') = 1)
EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltriFamiglie]
WITH CHECK 
ADD CONSTRAINT [FK_QuickFiltriFamiglie_QuickFiltri]
FOREIGN KEY ([IDFiltro])
 REFERENCES [GP].[QuickFiltri]
            ([IDFiltro])
ON DELETE NO ACTION
ON UPDATE NO ACTION;'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

BEGIN TRY

EXEC sp_executesql N'ALTER TABLE [GP].[QuickFiltriFamiglie] CHECK CONSTRAINT [FK_QuickFiltriFamiglie_QuickFiltri];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
/* ---------------------------------------- *\
|* Changing login for fimet (Database User) *|
\* ---------------------------------------- */

BEGIN TRY

EXEC sp_executesql N'ALTER USER [fimet]
WITH DEFAULT_SCHEMA = [dbo], LOGIN = [fimet];'

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO
DECLARE @errorCount int
SELECT @errorCount = COUNT(*) FROM [#SchemaModificationErrors]
IF ISNULL(@errorCount, 0) != 0
BEGIN
  IF @@TRANCOUNT > 0 
    ROLLBACK TRANSACTION
  if @errorCount = 1 
  BEGIN
    PRINT 'Schema modification failed with 1 error.'
    SELECT [Error], [Message] FROM #SchemaModificationErrors
  END
  ELSE
  BEGIN
    PRINT 'Schema modification failed with ' + convert(varchar, @errorCount) + ' errors.'     
    SELECT [Error], [Message] FROM #SchemaModificationErrors
  END
END
ELSE
BEGIN
  IF @@TRANCOUNT > 0 
  BEGIN
    PRINT 'Schema modification successful'
    COMMIT TRANSACTION
  END
END
GO
DROP TABLE #SchemaModificationErrors
GO

/* --------------------------------------------- */
/* Update Lotti CampoTipo.sql */
update gp.lotti
set tipo = 0
where giacenza = 0

update gp.lotti
set tipo = 1
where giacenza <> 0

/* --------------------------------------------- */
declare @IDFormula int
declare @IDComposizione int
declare @IDColore int
declare @DescrizioneFormula varchar(150)
declare @IDColoreRotturaChiave VARCHAR(50)

update gp.composizionecolori set idformula = null
delete gp.formule
DBCC CHECKIDENT ('gp.formule', RESEED, 0)

DECLARE db_cursor CURSOR FOR
  select IDComposizione, IDColore from GP.ComposizioneColori order by IDColore

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @IDComposizione, @IDColore

set @IDColoreRotturaChiave = @IDColore
SET @DescrizioneFormula = 'Formula per Colore ID ' + CAST(@IDColore As VARCHAR(20))

insert into gp.Formule (Descrizione) values (@DescrizioneFormula)
SELECT @IDFormula = @@IDENTITY
  
WHILE @@FETCH_STATUS = 0  
BEGIN 

  SET @DescrizioneFormula = 'Formula per Colore ID ' + CAST(@IDColore As VARCHAR(20))
  
  IF @IDColoreRotturaChiave = @IDColore
    BEGIN
      UPDATE GP.ComposizioneColori
	  SET GP.ComposizioneColori.IDFormula = @IDFormula
	  WHERE GP.ComposizioneColori.IDcomposizione = @IDComposizione
    END
    ELSE
      BEGIN
        insert into gp.Formule (Descrizione) values (@DescrizioneFormula)
        SELECT @IDFormula = @@IDENTITY
        
        UPDATE GP.ComposizioneColori
		SET GP.ComposizioneColori.IDFormula = @IDFormula
		WHERE GP.ComposizioneColori.IDcomposizione = @IDComposizione

		set @IDColoreRotturaChiave = @IDColore
      END
      
  FETCH NEXT FROM db_cursor INTO @IDComposizione, @IDColore
  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor 

/* --------------------------------------------- */
/* [GestionePolveri].[GP].[FamiglieColoriXPolveri] */
;WITH cteColoriAbbinati(IDPolvere, IDFamiglia) AS 
(
	select cc.idpolvere, ac.idFamiglia
	FROM  gp.composizionecolori as cc 
	inner join gp.anagcolori as ac on cc.idcolore = ac.idcolore
	inner join gp.anagpolveri as ap on cc.idpolvere = ap.idpolvere 
)
insert into [GP].[FamiglieColoriXPolveri]
select  cte.IDPolvere
        , Stuff(( select distinct ',' + cast(Sub.IDFamiglia as varchar) AS [text()] from cteColoriAbbinati as Sub where Sub.IDPolvere = AP.IdPolvere for xml path('')), 1, 1, '') as 'Famiglie'
        , null
from cteColoriAbbinati as cte
inner join GP.AnagPolveri as AP on cte.IDPolvere = AP.IDPolvere
group by cte.idpolvere, AP.IDPolvere

/* --------------------------------------------- */ 
/*  Update campo Applicazione per FamiglieColoriXPolveri.sql  */
;WITH cteColoriAbbinati(IDPolvere, Applicazione) AS 
(
    SELECT DISTINCT A.IDPolvere, CC.Applicazione
    FROM GP.AnagPolveri as A
    INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
)
update [GP].[FamiglieColoriXPolveri]
set [GP].[FamiglieColoriXPolveri].[Applicazione]
    = Stuff(( select distinct ',' + cast(Sub.Applicazione as varchar) AS [text()] from cteColoriAbbinati as Sub where Sub.IDPolvere = T.IdPolvere for xml path('')), 1, 1, '')
from cteColoriAbbinati as cte
inner join [GP].[FamiglieColoriXPolveri] as T
ON T.IDPolvere = cte.IDPolvere